package com.gome.ocean.common.utils;

import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Collection;
import java.util.HashMap;
import java.util.HashSet;
import java.util.List;
import java.util.Map;
import java.util.Properties;
import java.util.Set;
import java.util.concurrent.Callable;
import java.util.concurrent.ExecutorService;
import java.util.concurrent.Executors;
import java.util.concurrent.Future;
import java.util.concurrent.TimeUnit;

import org.apache.commons.lang3.StringUtils;
import org.apache.commons.lang3.exception.ExceptionUtils;
import org.apache.commons.lang3.tuple.ImmutableTriple;
import org.apache.commons.lang3.tuple.Triple;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import com.alibaba.druid.sql.parser.SQLParserUtils;
import com.alibaba.druid.sql.parser.SQLStatementParser;
import com.gome.ocean.common.constants.WebSqlContants;
import com.gome.ocean.common.enums.DataBaseType;
import com.gome.ocean.common.enums.TransactionIsolation;
import com.gome.ocean.common.exception.DataConnectionException;
import com.google.common.collect.Lists;
import com.google.common.collect.Maps;
import com.google.common.collect.Sets;
import com.google.common.util.concurrent.ThreadFactoryBuilder;

public final class DBUtil {

    private static final Logger LOG                     = LoggerFactory.getLogger(DBUtil.class);
    private static final int    SOCKET_TIMEOUT_INSECOND = 172800;
    private static final int    TIMEOUT_SECONDS         = 3;

    private static final ThreadLocal<ExecutorService> rsExecutors = new ThreadLocal<ExecutorService>() {

        @Override
        protected ExecutorService initialValue() {
            return Executors.newFixedThreadPool(1,
                new ThreadFactoryBuilder().setNameFormat("rsExecutors-%d").setDaemon(true).build());
        }
    };

    private DBUtil(){
    }

    public static String chooseJdbcUrl(final DataBaseType dataBaseType, final List<String> jdbcUrls,
                                       final String username, final String password, final List<String> preSql,
                                       final boolean checkSlave) {

        if (null == jdbcUrls || jdbcUrls.isEmpty()) {
            throw new DataConnectionException(
                String.format("您的jdbcUrl的配置信息有错, 因为jdbcUrl[%s]不能为空. 请检查您的配置并作出修改.", StringUtils.join(jdbcUrls, ",")));
        }

        try {
            return RetryUtil.executeWithRetry(new Callable<String>() {

                @Override
                public String call() throws Exception {
                    boolean connOK = false;
                    for (String url : jdbcUrls) {
                        if (StringUtils.isNotBlank(url)) {
                            url = url.trim();
                            if (null != preSql && !preSql.isEmpty()) {
                                connOK = testConnWithoutRetry(dataBaseType, url, username, password, preSql);
                            } else {
                                connOK = testConnWithoutRetry(dataBaseType, url, username, password, checkSlave);
                            }
                            if (connOK) {
                                return url;
                            }
                        }
                    }
                    throw new DataConnectionException(
                        "无法连接对应的数据库，可能原因是：1) 配置的ip/port/database/jdbc错误，无法连接。2) 配置的username/password错误，鉴权失败。请和DBA确认该数据库的连接信息是否正确。");
                }
            }, 3, 1000L, true);
        } catch (Exception e) {
            throw new DataConnectionException(String.format(
                "数据库连接失败. 因为根据您配置的连接信息,无法从:%s 中找到可连接的jdbcUrl. 请检查您的配置并作出修改.", StringUtils.join(jdbcUrls, ",")), e);
        }
    }

    public static String chooseJdbcUrlWithoutRetry(final DataBaseType dataBaseType, final List<String> jdbcUrls,
                                                   final String username, final String password,
                                                   final List<String> preSql,
                                                   final boolean checkSlave) throws DataConnectionException {

        if (null == jdbcUrls || jdbcUrls.isEmpty()) {
            throw new DataConnectionException(
                String.format("您的jdbcUrl的配置信息有错, 因为jdbcUrl[%s]不能为空. 请检查您的配置并作出修改.", StringUtils.join(jdbcUrls, ",")));
        }

        boolean connOK = false;
        for (String url : jdbcUrls) {
            if (StringUtils.isNotBlank(url)) {
                url = url.trim();
                if (null != preSql && !preSql.isEmpty()) {
                    connOK = testConnWithoutRetry(dataBaseType, url, username, password, preSql);
                } else {
                    try {
                        connOK = testConnWithoutRetry(dataBaseType, url, username, password, checkSlave);
                    } catch (Exception e) {
                        throw new DataConnectionException(
                            String.format("数据库连接失败. 因为根据您配置的连接信息,无法从:%s 中找到可连接的jdbcUrl. 请检查您的配置并作出修改.",
                                StringUtils.join(jdbcUrls, ",")),
                            e);
                    }
                }
                if (connOK) {
                    return url;
                }
            }
        }
        throw new DataConnectionException(String.format("数据库连接失败. 因为根据您配置的连接信息,无法从:%s 中找到可连接的jdbcUrl. 请检查您的配置并作出修改.",
            StringUtils.join(jdbcUrls, ",")));
    }

    /**
     * 检查slave的库中的数据是否已到凌晨00:00 如果slave同步的数据还未到00:00返回false 否则范围true
     *
     * @author ZiChi
     * @version 1.0 2014-12-01
     */
    private static boolean isSlaveBehind(Connection conn) {
        try {
            ResultSet rs = query(conn, "SHOW VARIABLES LIKE 'read_only'");
            if (DBUtil.asyncResultSetNext(rs)) {
                String readOnly = rs.getString("Value");
                if ("ON".equalsIgnoreCase(readOnly)) { // 备库
                    ResultSet rs1 = query(conn, "SHOW SLAVE STATUS");
                    if (DBUtil.asyncResultSetNext(rs1)) {
                        String ioRunning = rs1.getString("Slave_IO_Running");
                        String sqlRunning = rs1.getString("Slave_SQL_Running");
                        long secondsBehindMaster = rs1.getLong("Seconds_Behind_Master");
                        if ("Yes".equalsIgnoreCase(ioRunning) && "Yes".equalsIgnoreCase(sqlRunning)) {
                            ResultSet rs2 = query(conn, "SELECT TIMESTAMPDIFF(SECOND, CURDATE(), NOW())");
                            DBUtil.asyncResultSetNext(rs2);
                            long secondsOfDay = rs2.getLong(1);
                            return secondsBehindMaster > secondsOfDay;
                        } else {
                            return true;
                        }
                    } else {
                        LOG.warn("SHOW SLAVE STATUS has no result");
                    }
                }
            } else {
                LOG.warn("SHOW VARIABLES like 'read_only' has no result");
            }
        } catch (Exception e) {
            LOG.warn("checkSlave failed, errorMessage:[{}].", e.getMessage());
        }
        return false;
    }

    /**
     * 检查表是否具有insert 权限 insert on *.* 或者 insert on database.* 时验证通过 当insert on
     * database.tableName时，确保tableList中的所有table有insert 权限，验证通过 其它验证都不通过
     *
     * @author ZiChi
     * @version 1.0 2015-01-28
     */
    public static boolean hasInsertPrivilege(DataBaseType dataBaseType, String jdbcURL, String userName,
                                             String password, List<String> tableList) {
        /* 准备参数 */

        String[] urls = jdbcURL.split("/");
        String dbName;
        if (urls != null && urls.length != 0) {
            dbName = urls[3];
        } else {
            return false;
        }

        String dbPattern = "`" + dbName + "`.*";
        Collection<String> tableNames = new HashSet<String>(tableList.size());
        tableNames.addAll(tableList);

        Connection connection = connect(dataBaseType, jdbcURL, userName, password);
        try {
            ResultSet rs = query(connection, "SHOW GRANTS FOR " + userName);
            while (DBUtil.asyncResultSetNext(rs)) {
                String grantRecord = rs.getString("Grants for " + userName + "@%");
                String[] params = grantRecord.split("\\`");
                if (params != null && params.length >= 3) {
                    String tableName = params[3];
                    if (params[0].contains("INSERT") && !tableName.equals("*") && tableNames.contains(tableName))
                        tableNames.remove(tableName);
                } else {
                    if (grantRecord.contains("INSERT") || grantRecord.contains("ALL PRIVILEGES")) {
                        if (grantRecord.contains("*.*")) return true;
                        else if (grantRecord.contains(dbPattern)) {
                            return true;
                        }
                    }
                }
            }
        } catch (Exception e) {
            LOG.warn("Check the database has the Insert Privilege failed, errorMessage:[{}]", e.getMessage());
        }
        if (tableNames.isEmpty()) return true;
        return false;
    }

    public static boolean checkInsertPrivilege(DataBaseType dataBaseType, String jdbcURL, String userName,
                                               String password, List<String> tableList) {
        Connection connection = connect(dataBaseType, jdbcURL, userName, password);
        String insertTemplate = "insert into %s(select * from %s where 1 = 2)";

        boolean hasInsertPrivilege = true;
        Statement insertStmt = null;
        for (String tableName : tableList) {
            String checkInsertPrivilegeSql = String.format(insertTemplate, tableName, tableName);
            try {
                insertStmt = connection.createStatement();
                executeSqlWithoutResultSet(insertStmt, checkInsertPrivilegeSql);
            } catch (Exception e) {
                if (DataBaseType.Oracle.equals(dataBaseType)) {
                    if (e.getMessage() != null && e.getMessage().contains("insufficient privileges")) {
                        hasInsertPrivilege = false;
                        LOG.warn("User [" + userName + "] has no 'insert' privilege on table[" + tableName
                                 + "], errorMessage:[{}]",
                            e.getMessage());
                    }
                } else {
                    hasInsertPrivilege = false;
                    LOG.warn("User [" + userName + "] has no 'insert' privilege on table[" + tableName
                             + "], errorMessage:[{}]",
                        e.getMessage());
                }
            }
        }
        try {
            connection.close();
        } catch (SQLException e) {
            LOG.warn("connection close failed, " + e.getMessage());
        }
        return hasInsertPrivilege;
    }

    public static boolean checkDeletePrivilege(DataBaseType dataBaseType, String jdbcURL, String userName,
                                               String password, List<String> tableList) {
        Connection connection = connect(dataBaseType, jdbcURL, userName, password);
        String deleteTemplate = "delete from %s WHERE 1 = 2";

        boolean hasInsertPrivilege = true;
        Statement deleteStmt = null;
        for (String tableName : tableList) {
            String checkDeletePrivilegeSQL = String.format(deleteTemplate, tableName);
            try {
                deleteStmt = connection.createStatement();
                executeSqlWithoutResultSet(deleteStmt, checkDeletePrivilegeSQL);
            } catch (Exception e) {
                hasInsertPrivilege = false;
                LOG.warn(
                    "User [" + userName + "] has no 'delete' privilege on table[" + tableName + "], errorMessage:[{}]",
                    e.getMessage());
            }
        }
        try {
            connection.close();
        } catch (SQLException e) {
            LOG.warn("connection close failed, " + e.getMessage());
        }
        return hasInsertPrivilege;
    }

    /**
     * Get direct JDBC connection
     * <p/>
     * if connecting failed, try to connect for MAX_TRY_TIMES times
     * <p/>
     * NOTE: In DataX, we don't need connection pool in fact
     */
    public static Connection getConnection(final DataBaseType dataBaseType, final String jdbcUrl, final String username,
                                           final String password) {

        return getConnection(dataBaseType, jdbcUrl, username, password, String.valueOf(SOCKET_TIMEOUT_INSECOND * 1000));
    }

    /**
     * @param dataBaseType
     * @param jdbcUrl
     * @param username
     * @param password
     * @param socketTimeout 设置socketTimeout，单位ms，String类型
     * @return
     */
    public static Connection getConnection(final DataBaseType dataBaseType, final String jdbcUrl, final String username,
                                           final String password, final String socketTimeout) {

        try {
            return DBUtil.connect(dataBaseType, jdbcUrl, username, password, socketTimeout);
        } catch (Exception e) {
            throw new DataConnectionException(
                String.format("数据库连接失败. 因为根据您配置的连接信息:%s获取数据库连接失败. 请检查您的配置并作出修改.", jdbcUrl), e);
        }
    }

    /**
     * Get direct JDBC connection
     * <p/>
     * if connecting failed, try to connect for MAX_TRY_TIMES times
     * <p/>
     * NOTE: In DataX, we don't need connection pool in fact
     */
    public static Connection getConnectionWithoutRetry(final DataBaseType dataBaseType, final String jdbcUrl,
                                                       final String username, final String password) {
        return getConnectionWithoutRetry(dataBaseType,
            jdbcUrl,
            username,
            password,
            String.valueOf(SOCKET_TIMEOUT_INSECOND * 1000));
    }

    public static Connection getConnectionWithoutRetry(final DataBaseType dataBaseType, final String jdbcUrl,
                                                       final String username, final String password,
                                                       String socketTimeout) {
        return DBUtil.connect(dataBaseType, jdbcUrl, username, password, socketTimeout);
    }

    private static synchronized Connection connect(DataBaseType dataBaseType, String url, String user, String pass) {
        return connect(dataBaseType, url, user, pass, String.valueOf(SOCKET_TIMEOUT_INSECOND * 1000));
    }

    private static synchronized Connection connect(DataBaseType dataBaseType, String url, String user, String pass,
                                                   String socketTimeout) {
        Properties prop = new Properties();
        prop.put("user", user);
        prop.put("password", pass);

        if (dataBaseType == DataBaseType.Oracle) {
            // oracle.net.READ_TIMEOUT for jdbc versions < 10.1.0.5
            // oracle.jdbc.ReadTimeout for jdbc versions >=10.1.0.5
            // unit ms
            prop.put("oracle.jdbc.ReadTimeout", socketTimeout);
        }

        return connect(dataBaseType, url, prop);
    }

    private static synchronized Connection connect(DataBaseType dataBaseType, String url, Properties prop) {
        try {
            Class.forName(dataBaseType.getDriverClassName());
            DriverManager.setLoginTimeout(TIMEOUT_SECONDS);
            return DriverManager.getConnection(url, prop);
        } catch (Exception e) {
            throw new DataConnectionException("数据库连接错误!", e);
        }
    }

    /**
     * a wrapped method to execute select-like sql statement .
     *
     * @param conn Database connection .
     * @param sql sql statement to be executed
     * @return a {@link ResultSet}
     * @throws SQLException if occurs SQLException.
     */
    public static ResultSet query(Connection conn, String sql, int fetchSize) throws SQLException {
        // 默认3600 s 的query Timeout
        return query(conn, sql, fetchSize, SOCKET_TIMEOUT_INSECOND);
    }

    /**
     * a wrapped method to execute select-like sql statement .
     *
     * @param conn Database connection .
     * @param sql sql statement to be executed
     * @param fetchSize
     * @param queryTimeout unit:second
     * @return
     * @throws SQLException
     */
    public static ResultSet query(Connection conn, String sql, int fetchSize, int queryTimeout) throws SQLException {
        // make sure autocommit is off
        conn.setAutoCommit(false);
        Statement stmt = conn.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY);
        stmt.setFetchSize(fetchSize);
        stmt.setQueryTimeout(queryTimeout);
        return query(stmt, sql);
    }

    /**
     * a wrapped method to execute select-like sql statement .
     *
     * @param stmt {@link Statement}
     * @param sql sql statement to be executed
     * @return a {@link ResultSet}
     * @throws SQLException if occurs SQLException.
     */
    public static ResultSet query(Statement stmt, String sql) throws SQLException {
        return stmt.executeQuery(sql);
    }

    public static int update(Statement stmt, String sql) throws SQLException {
        return stmt.executeUpdate(sql);
    }

    public static void executeSqlWithoutResultSet(Statement stmt, String sql) throws SQLException {
        stmt.execute(sql);
    }

    /**
     * Close {@link ResultSet}, {@link Statement} referenced by this
     * {@link ResultSet}
     *
     * @param rs {@link ResultSet} to be closed
     * @throws IllegalArgumentException
     */
    public static void closeResultSet(ResultSet rs) {
        try {
            if (null != rs) {
                Statement stmt = rs.getStatement();
                if (null != stmt) {
                    stmt.close();
                    stmt = null;
                }
                rs.close();
            }
            rs = null;
        } catch (SQLException e) {
            throw new IllegalStateException(e);
        }
    }

    public static void closeDBResources(ResultSet rs, Statement stmt, Connection conn) {
        if (null != rs) {
            try {
                rs.close();
            } catch (SQLException unused) {
            }
        }

        if (null != stmt) {
            try {
                stmt.close();
            } catch (SQLException unused) {
            }
        }

        if (null != conn) {
            try {
                conn.close();
            } catch (SQLException unused) {
            }
        }
    }

    public static void closeDBResources(Statement stmt, Connection conn) {
        closeDBResources(null, stmt, conn);
    }

    public static List<String> getTableColumns(DataBaseType dataBaseType, String jdbcUrl, String user, String pass,
                                               String tableName) {
        Connection conn = getConnection(dataBaseType, jdbcUrl, user, pass);
        return getTableColumnsByConn(dataBaseType, conn, tableName);
    }

    public static List<String> getTableColumns(Connection conn, DataBaseType dataBaseType, String tableName) {
        return getTableColumnsByConn(dataBaseType, conn, tableName);
    }

    public static List<String> getTables(DataBaseType dataBaseType, String jdbcUrl, String user, String pass) {
        Connection conn = getConnection(dataBaseType, jdbcUrl, user, pass);
        return getTablesByConn(dataBaseType, conn, user);
    }

    public static List<String> getTablesByConn(DataBaseType dataBaseType, Connection conn, String dbUser) {
        List<String> tables = new ArrayList<String>();
        Statement statement = null;
        ResultSet rs = null;
        String queryColumnSql = null;
        try {
            statement = conn.createStatement();
            if (DataBaseType.Oracle == dataBaseType) {
                queryColumnSql = "SELECT TABLE_NAME,OWNER FROM ALL_TABLES WHERE OWNER='" + dbUser.toUpperCase() + "'";
            } else if (DataBaseType.MySql == dataBaseType) {
                queryColumnSql = "SHOW TABLE STATUS";
            } else if (DataBaseType.DRDS == dataBaseType) {
                queryColumnSql = "SHOW TABLES";
            } else if (DataBaseType.SQLSERVER == dataBaseType) {
                queryColumnSql = "select * from sys.tables";
            }
            rs = statement.executeQuery(queryColumnSql);
            while (rs.next()) {
                tables.add(rs.getString(1));
            }
        } catch (SQLException e) {
            throw new DataConnectionException("获取表字段错误!", e);
        } finally {
            DBUtil.closeDBResources(rs, statement, conn);
        }
        return tables;
    }

    public static List<String> getTablesByConnAdmin(DataBaseType dataBaseType, Connection conn, Long adId) {
        List<String> tables = new ArrayList<String>();
        Statement statement = null;
        ResultSet rs = null;
        String queryColumnSql = null;
        try {
            statement = conn.createStatement();
            queryColumnSql = "select table_name from info_tables where adid = " + adId;
            rs = statement.executeQuery(queryColumnSql);
            while (rs.next()) {
                String name = rs.getString(1);
                if (!StringUtils.startsWithIgnoreCase(name, "MLOG$_")
                    && !StringUtils.startsWithIgnoreCase(name, "RUPD$_")) {
                    tables.add(name);
                }
            }
            return tables;
        } catch (SQLException e) {
            throw new DataConnectionException("获取表字段错误!", e);
        } finally {
            DBUtil.closeDBResources(rs, statement, conn);
        }
    }

    public static List<String> getTablesByConn(DataBaseType dataBaseType, Connection conn, String dbName, String dbUser,
                                               String[] types) {
        List<String> tables = new ArrayList<String>();
        Statement statement = null;
        ResultSet rs = null;
        String queryColumnSql = null;
        try {
            String type = types[0];
            statement = conn.createStatement();
            if (DataBaseType.Oracle == dataBaseType) {
                if (type.equalsIgnoreCase("VIEW")) {
                    queryColumnSql = "select * from all_views where OWNER = '" + dbName.toUpperCase() + "'";
                } else {
//                    queryColumnSql = "select * from all_tab_privs where grantee = '" + dbUser.toUpperCase()
//                                     + "' AND table_schema = '" + dbName.toUpperCase() + "'";
                    queryColumnSql = "select * from all_tables  where owner = '" + dbName.toUpperCase() + "'";
                }
                rs = statement.executeQuery(queryColumnSql);
                while (rs.next()) {
                    String name = "";
                    if (type.equalsIgnoreCase("VIEW")) {
                        name = rs.getString("VIEW_NAME");
                    } else {
                        name = rs.getString("TABLE_NAME");
                    }
                    if (!StringUtils.startsWithIgnoreCase(name, "MLOG$_")
                        && !StringUtils.startsWithIgnoreCase(name, "RUPD$_")) {
                        tables.add(name);
                    }
                }
                return tables;
            } else {
                DatabaseMetaData metaData = conn.getMetaData();
                rs = metaData.getTables(null, dbName, null, types);
                while (rs.next()) {
                    String name = rs.getString(3);
                    if (!StringUtils.startsWithIgnoreCase(name, "MLOG$_")
                        && !StringUtils.startsWithIgnoreCase(name, "RUPD$_")) {
                        tables.add(name);
                    }
                }
                return tables;
            }
        } catch (SQLException e) {
            throw new DataConnectionException("获取表字段错误!", e);
        } finally {
            DBUtil.closeDBResources(rs, statement, conn);
        }
    }

    public static List<String> getDataBaseByConn(DataBaseType dataBaseType, Connection conn, String dbName) {
        List<String> tables = new ArrayList<String>();
        ResultSet rs = null;
        try {
            DatabaseMetaData dbmd = conn.getMetaData();
            if (DataBaseType.MySql == dataBaseType || DataBaseType.DRDS == dataBaseType) {
                rs = dbmd.getCatalogs();
            } else if (DataBaseType.Oracle == dataBaseType) {
                rs = dbmd.getSchemas();
            } else {
                return tables;
            }
            while (rs.next()) {
                String tableSchem = rs.getString(1);
                tables.add(tableSchem);
            }
        } catch (SQLException e) {
            throw new DataConnectionException("获取库信息错误!", e);
        } finally {
            DBUtil.closeDBResources(rs, null, conn);
        }
        return tables;
    }

    public static List<String> getTableColumnsByConn(DataBaseType dataBaseType, Connection conn, String tableName) {
        List<String> columns = new ArrayList<String>();
        Statement statement = null;
        ResultSet rs = null;
        String queryColumnSql = null;
        try {
            statement = conn.createStatement();
            queryColumnSql = String.format("select * from %s where 1=2", tableName);
            rs = statement.executeQuery(queryColumnSql);
            ResultSetMetaData rsMetaData = rs.getMetaData();
            for (int i = 0, len = rsMetaData.getColumnCount(); i < len; i++) {
                columns.add(rsMetaData.getColumnName(i + 1));
            }

        } catch (SQLException e) {
            throw new DataConnectionException("获取表字段错误!", e);
        } finally {
            DBUtil.closeDBResources(rs, statement, conn);
        }

        return columns;
    }

    @SuppressWarnings("resource")
    public static Map<String, Object> getColumnInfoMetaData(DataBaseType dataBaseType, Connection conn, String dbName,
                                                            String tableName, String sql) {
        Map<String, Object> resultMaps = new HashMap<String, Object>();
        Set<TableStruct> structSets = Sets.newLinkedHashSet();
        List<String> priList = new ArrayList<String>();
        ResultSet resultSet = null;
        try {
            if (StringUtils.isNotEmpty(tableName)) {
                DatabaseMetaData dmd = conn.getMetaData();
                if (DataBaseType.MySql == dataBaseType || DataBaseType.DRDS == dataBaseType) {
                    resultSet = dmd.getPrimaryKeys(dbName, null, tableName);
                } else if (DataBaseType.Oracle == dataBaseType) {
                    resultSet = dmd.getPrimaryKeys(null, dbName, tableName);
                }
                while (resultSet.next()) {
                    String columnName = resultSet.getString("COLUMN_NAME");
                    short keySeq = resultSet.getShort("KEY_SEQ");
                    if (StringUtils.isNotEmpty(columnName) && keySeq > 0) {
                        priList.add(columnName);
                    }
                }
            }
            resultSet = query(conn, sql);
            List<Map<String, Object>> dataList = new ArrayList<Map<String, Object>>();
            ResultSetMetaData metaData = resultSet.getMetaData();
            for (int i = 0; i < metaData.getColumnCount(); i++) {
                TableStruct column = new TableStruct();
                String columnName = metaData.getColumnName(i + 1);
                String columnType = metaData.getColumnTypeName(i + 1);
                if (columnName.equals("ROW_ID") || columnType.toLowerCase().contains("rowid")
                    || columnType.toLowerCase().contains("rownum")) continue;
                Integer columnSize = metaData.getColumnDisplaySize(i + 1);
                column.setTableName(tableName);
                column.setColumnName(columnName);
                column.setColumnType(columnType);
                column.setColumnSize(columnSize != null ? String.valueOf(columnSize.intValue()) : "");
                boolean isAutoIncrement = metaData.isAutoIncrement(i + 1);
                if (isAutoIncrement) {
                    column.setIsAutoIncrement(true);
                } else {
                    column.setIsAutoIncrement(false);
                }
                int isNullNum = metaData.isNullable(i + 1);
                if (isNullNum == 0) {
                    column.setNull(false);
                } else {
                    column.setNull(true);
                }
                structSets.add(column);
            }
            while (resultSet.next()) {
                Map<String, Object> dataMaps = new HashMap<String, Object>();
                for (int i = 0; i < metaData.getColumnCount(); i++) {
                    dataMaps.put("COLUMN_" + (i + 1), resultSet.getString(i + 1));
                }
                dataList.add(dataMaps);
            }
            // 类型与备注名称因JAVA_API中没有兼容 ,所以只能分开获取
            DatabaseMetaData dataMeta = conn.getMetaData();
            if (DataBaseType.MySql == dataBaseType || DataBaseType.DRDS == dataBaseType) {
                resultSet = dataMeta.getColumns(dbName, null, tableName, "%");
            } else {
                resultSet = dataMeta.getColumns(null, dbName, tableName, "%");
            }
            while (resultSet.next()) {
                String currentColName = resultSet.getString("COLUMN_NAME");
                String remarks = resultSet.getString("REMARKS") == null ? "" : resultSet.getString("REMARKS");
                for (TableStruct struct : structSets) {
                    if (struct.getColumnName().equals(currentColName)) {
                        struct.setColumnComment(remarks);
                    }
                }
            }
            resultMaps.put("primaryKeys", priList);
            resultMaps.put("structSets", structSets);
            resultMaps.put("data", dataList);
            return resultMaps;
        } catch (SQLException e) {
            throw new DataConnectionException(String.format("获取结构数据失败,异常信息:%s", ExceptionUtils.getMessage(e)), e);
        } finally {
            DBUtil.closeDBResources(resultSet, null, conn);
        }
    }

    /**
     * @return Left:ColumnName Middle:ColumnType Right:ColumnTypeName
     */
    public static Triple<List<String>, List<Integer>, List<String>> getColumnMetaData(DataBaseType dataBaseType,
                                                                                      String jdbcUrl, String user,
                                                                                      String pass, String tableName,
                                                                                      String column) {
        Connection conn = null;
        try {
            conn = getConnection(dataBaseType, jdbcUrl, user, pass);
            return getColumnMetaData(conn, tableName, column);
        } finally {
            DBUtil.closeDBResources(null, null, conn);
        }
    }

    @SuppressWarnings("resource")
    public static Integer[] getColumnAndIndexCount(DataBaseType dataBaseType, Connection conn, String dbName,
                                                   String tableName, String sql) {
        Integer[] columnAndPk = new Integer[2];
        ResultSet rs = null;
        try {
            DatabaseMetaData dmd = conn.getMetaData();
            if (DataBaseType.MySql == dataBaseType || DataBaseType.DRDS == dataBaseType) {
                rs = dmd.getIndexInfo(dbName, null, tableName, false, false);
            } else if (DataBaseType.Oracle == dataBaseType) {
                String indexSql = "SELECT unique(ai.OWNER),ai.INDEX_NAME,ai.INDEX_TYPE,ai.TABLE_OWNER,ai.TABLE_NAME,ai.TABLE_TYPE,ai.UNIQUENESS,ai.COMPRESSION,ai.STATUS,aic.COLUMN_NAME,aic.DESCEND,ai.LAST_ANALYZED FROM all_indexes ai left join  all_ind_columns aic on  ai.INDEX_NAME = aic.INDEX_NAME  where ai.table_name = '"
                                  + tableName + "' AND ai.TABLE_OWNER ='" + dbName + "' order by ai.LAST_ANALYZED desc";
                rs = query(conn, indexSql);
            }
            int pk = 0;
            while (rs.next()) {
                String indexName = rs.getString("INDEX_NAME");
                if (indexName != null && StringUtils.isNoneBlank(indexName)) {
                    pk++;
                }
            }
            int column = 0;
            if (DataBaseType.DRDS == dataBaseType) {
                rs = dmd.getColumns(null, dbName, tableName, "%");
                while (rs.next()) {
                    column++;
                }
            } else {
                rs = query(conn, sql);
                ResultSetMetaData rsmd = rs.getMetaData();
                for (int i = 0; i < rsmd.getColumnCount(); i++) {
                    String columnName = rsmd.getColumnName(i + 1);
                    String columnType = rsmd.getColumnTypeName(i + 1);
                    if (columnName.equals("ROW_ID") || columnType.toLowerCase().contains("rowid")
                        || columnType.toLowerCase().contains("rownum")) continue;
                    column++;
                }
            }
            columnAndPk[0] = pk;
            columnAndPk[1] = column;
            return columnAndPk;
        } catch (SQLException e) {
            throw new DataConnectionException(
                String.format("获取字段及索引数量失败. 请联系 DBA 核查该库、表信息.异常信息:%s", ExceptionUtils.getMessage(e)), e);
        } finally {
            DBUtil.closeDBResources(null, null, conn);
        }
    }

    public static List<Map<String, String>> getColumnDetail(DataBaseType dataBaseType, Connection conn, String dbName,
                                                            String tableName, String sql) {
        List<Map<String, String>> listMaps = new ArrayList<Map<String, String>>();
        ResultSet rs = null;
        try {
            if (DataBaseType.DRDS == dataBaseType) {
                DatabaseMetaData dmd = conn.getMetaData();
                rs = dmd.getColumns(null, dbName, tableName, "%");
                while (rs.next()) {
                    Map<String, String> columnMap = new HashMap<String, String>();
                    String columnName = rs.getString("COLUMN_NAME");
                    String columnType = rs.getString("TYPE_NAME");
                    String columnSize = rs.getString("COLUMN_SIZE");
                    columnMap.put("columnName", columnName);
                    if (StringUtils.isNotEmpty(columnSize)) {
                        columnMap.put("columnType", columnType.concat("(" + columnSize + ")"));
                    } else {
                        columnMap.put("columnType", columnType);
                    }
                    listMaps.add(columnMap);
                }
            } else {
                rs = query(conn, sql);
                ResultSetMetaData rsmd = rs.getMetaData();
                for (int i = 0; i < rsmd.getColumnCount(); i++) {
                    Map<String, String> columnMap = new HashMap<String, String>();
                    String columnName = rsmd.getColumnName(i + 1);
                    String columnType = rsmd.getColumnTypeName(i + 1);
                    if (columnName.equals("ROW_ID") || columnType.toLowerCase().contains("rowid")
                        || columnType.toLowerCase().contains("rownum")) continue;
                    int columnSize = rsmd.getColumnDisplaySize(i + 1);
                    columnMap.put("columnName", columnName);
                    columnMap.put("columnType", columnType.concat("(" + columnSize + ")"));
                    listMaps.add(columnMap);
                }
            }
            return listMaps;
        } catch (SQLException e) {
            throw new DataConnectionException(
                String.format("获取表:%s的字段失败. 请联系DBA核查该库、表信息.异常信息:%s", tableName, ExceptionUtils.getMessage(e)), e);
        } finally {
            DBUtil.closeDBResources(null, null, conn);
        }
    }

    /**
     * 获取索引详细信息
     */
    public static List<Map<String, Object>> getTableIndexDetailInfo(DataBaseType dataBaseType, Connection conn,
                                                                    String dbName, String tableName) {
        ResultSet rs = null;
        List<Map<String, Object>> indexMapLists = Lists.newArrayList();
        try {
            DatabaseMetaData metaData = conn.getMetaData();
            if (DataBaseType.MySql == dataBaseType || DataBaseType.DRDS == dataBaseType) {
                rs = metaData.getIndexInfo(dbName, null, tableName, false, false);
            } else if (DataBaseType.Oracle == dataBaseType) {
                String indexSql = "SELECT unique(ai.OWNER),ai.INDEX_NAME,ai.INDEX_TYPE,ai.TABLE_OWNER,ai.TABLE_NAME,ai.TABLE_TYPE,ai.UNIQUENESS,ai.COMPRESSION,ai.STATUS,aic.COLUMN_NAME,aic.DESCEND,ai.LAST_ANALYZED FROM all_indexes ai left join  all_ind_columns aic on  ai.INDEX_NAME = aic.INDEX_NAME  where ai.table_name = '"
                                  + tableName + "' AND ai.TABLE_OWNER ='" + dbName + "' order by ai.LAST_ANALYZED desc";
                rs = query(conn, indexSql);
            }
            while (rs.next()) {
                Map<String, Object> indexMap = Maps.newHashMap();
                String indexName = rs.getString("INDEX_NAME");
                if (indexName == null && StringUtils.isBlank(indexName)) continue;
                String columnName = rs.getString("COLUMN_NAME");
                if (DataBaseType.Oracle == dataBaseType) {
                    String uniqueName = rs.getString("UNIQUENESS");
                    if (uniqueName != null && StringUtils.isNoneBlank(uniqueName)) {
                        if (uniqueName.equals("UNIQUE")) {
                            indexMap.put("type", "UNIQUE");
                        } else {
                            indexMap.put("type", "INDEX");
                        }
                    }
                    String indexTypeName = rs.getString("INDEX_TYPE");
                    if (indexTypeName != null && StringUtils.isNotBlank(indexTypeName)) {
                        indType: switch (indexTypeName) {
                            case "NORMAL":
                                indexMap.put("method", "INDEX");
                                break indType;
                            case "BITMAP":
                                indexMap.put("method", "BITMAP");
                                break indType;
                            default:
                                indexMap.put("method", "");
                                break indType;
                        }
                    }
                } else {
                    Boolean nonUnique = rs.getBoolean("NON_UNIQUE"); // 是否可以不唯一
                    if (!nonUnique) {
                        indexMap.put("type", "UNIQUE");
                    } else {
                        indexMap.put("type", "NORMAL");
                    }
                    short type = rs.getShort("TYPE");
                    indexSwitch: switch (type) {
                        case 0:
                            indexMap.put("method", "");
                            break indexSwitch;
                        case 1:
                            indexMap.put("method", "BTREE");
                            break indexSwitch;
                        case 2:
                            indexMap.put("method", "HASH");
                            break indexSwitch;
                        case 3:
                            indexMap.put("method", "");
                            break indexSwitch;
                        default:
                            indexMap.put("method", "");
                            break indexSwitch;
                    }
                }
                indexMap.put("name", StringUtils.isNotBlank(indexName) ? indexName : "");
                indexMap.put("oldIndexName", StringUtils.isNotBlank(indexName) ? indexName : "");
                indexMap.put("columns", StringUtils.isNotBlank(columnName) ? columnName : "");
                indexMapLists.add(indexMap);
            }
            return indexMapLists;
        } catch (SQLException e) {
            throw new DataConnectionException(
                String.format("获取表:%s的索引详细信息失败.请联系DBA核查该库、表信息.异常信息:%s", tableName, ExceptionUtils.getMessage(e)), e);
        } finally {
            DBUtil.closeDBResources(rs, null, conn);
        }
    }

    /**
     * 获取外键详细信息
     */
    public static List<Map<String, Object>> getTableReferenceDetailInfo(DataBaseType dataBaseType, Connection conn,
                                                                        String dbName, String tableName) {
        ResultSet rs = null;
        List<Map<String, Object>> refMapLists = Lists.newArrayList();
        try {
            DatabaseMetaData metaData = conn.getMetaData();
            if (DataBaseType.MySql == dataBaseType || DataBaseType.DRDS == dataBaseType) {
                rs = metaData.getExportedKeys(dbName, null, tableName);
            } else if (DataBaseType.Oracle == dataBaseType) {
                String refSql = "SELECT unique(ac.OWNER),acc.COLUMN_NAME as FKCOLUMN_NAME,ac.CONSTRAINT_NAME as FK_NAME,ac.CONSTRAINT_TYPE,ac.TABLE_NAME,ac.DELETE_RULE,ac.STATUS,ac.DEFERRABLE,ac.DEFERRED,ac.VALIDATED,ac.GENERATED,ac.LAST_CHANGE,ac.INDEX_OWNER,ac.INDEX_NAME,ac.INVALID,ac.VIEW_RELATED FROM ALL_CONSTRAINTS ac left join ALL_CONS_COLUMNS acc  ON ac.CONSTRAINT_NAME = acc.CONSTRAINT_NAME WHERE ac.OWNER = '"
                                + dbName + "' AND ac.TABLE_NAME = '" + tableName
                                + "' AND ac.STATUS = 'ENABLED' ORDER BY ac.LAST_CHANGE DESC";
                rs = query(conn, refSql);
            }
            while (rs.next()) {
                Map<String, Object> refMap = Maps.newHashMap();
                String pkColumnName = "", fkName = "", fkDBName = "", fkTableName = "", fkColumnName = "";
                if (DataBaseType.MySql == dataBaseType || DataBaseType.DRDS == dataBaseType) {
                    pkColumnName = rs.getString("PKCOLUMN_NAME");
                    fkName = rs.getString("FK_NAME");
                    fkDBName = rs.getString("FKTABLE_SCHEM");
                    fkTableName = rs.getString("FKTABLE_NAME");
                    fkColumnName = rs.getString("FKCOLUMN_NAME");
                    short delRuleType = rs.getShort("DELETE_RULE");
                    delSwitch: switch (delRuleType) {
                        case 0:
                            refMap.put("deleteAction", "NO ACTION");
                            break delSwitch;
                        case 1:
                            refMap.put("deleteAction", "CASCADE");
                            break delSwitch;
                        case 2:
                            refMap.put("deleteAction", "SET NULL");
                            break delSwitch;
                        case 3:
                            refMap.put("deleteAction", "RESTRICT");
                            break delSwitch;
                        default:
                            refMap.put("deleteAction", "SET DEFAULT");
                            break delSwitch;
                    }
                    short updateRuleType = rs.getShort("UPDATE_RULE");
                    updateSwitch: switch (updateRuleType) {
                        case 0:
                            refMap.put("updateAction", "NO ACTION");
                            break updateSwitch;
                        case 1:
                            refMap.put("updateAction", "CASCADE");
                            break updateSwitch;
                        case 2:
                            refMap.put("updateAction", "SET NULL");
                            break updateSwitch;
                        case 3:
                            refMap.put("updateAction", "RESTRICT");
                            break updateSwitch;
                        default:
                            refMap.put("updateAction", "SET DEFAULT");
                            break updateSwitch;
                    }
                } else {
                    pkColumnName = rs.getString("FKCOLUMN_NAME");
                    fkName = rs.getString("FK_NAME");
                    fkDBName = dbName;
                    fkTableName = tableName;
                    fkColumnName = rs.getString("FKCOLUMN_NAME");
                    String delRuleType = rs.getString("DELETE_RULE");
                    if (delRuleType != null) {
                        delSwitch: switch (delRuleType) {
                            case "NO ACTION":
                                refMap.put("deleteAction", "NO ACTION");
                                break delSwitch;
                            case "CASCADE":
                                refMap.put("deleteAction", "CASCADE");
                                break delSwitch;
                            case "SET NULL":
                                refMap.put("deleteAction", "SET NULL");
                                break delSwitch;
                            default:
                                refMap.put("deleteAction", "NO ACTION");
                                break delSwitch;
                        }
                    } else {
                        refMap.put("deleteAction", "NO ACTION");
                    }
                    refMap.put("updateAction", "SET DEFAULT");
                }
                refMap.put("name", StringUtils.isNotBlank(fkName) ? fkName : "");
                refMap.put("oldRefName", StringUtils.isNotBlank(fkName) ? fkName : "");
                refMap.put("columns", StringUtils.isNotBlank(pkColumnName) ? pkColumnName : "");
                refMap.put("refDBName", StringUtils.isNotBlank(fkDBName) ? fkDBName : "");
                refMap.put("refTableName", StringUtils.isNotBlank(fkTableName) ? fkTableName : "");
                refMap.put("refColumns", StringUtils.isNotBlank(fkColumnName) ? fkColumnName : "");
                refMapLists.add(refMap);
            }
            return refMapLists;
        } catch (SQLException e) {
            throw new DataConnectionException(
                String.format("获取表:%s的外键详细信息失败.请联系DBA核查该库、表信息.异常信息:%s", tableName, ExceptionUtils.getMessage(e)), e);
        } finally {
            DBUtil.closeDBResources(rs, null, conn);
        }
    }

    /**
     * 获取存储过程/触发器/函数详细信息
     */
    public static List<Map<String, Object>> getTableProcedureFuncDetailData(DataBaseType dataBaseType, Connection conn,
                                                                            String dbName, String objName,
                                                                            String objType) {
        ResultSet rs = null;
        List<Map<String, Object>> proMapLists = Lists.newArrayList();
        try {
            DatabaseMetaData metaData = conn.getMetaData();
            if (DataBaseType.MySql == dataBaseType || DataBaseType.DRDS == dataBaseType) {
                if (StringUtils.isBlank(objName)) {
                    if (objType.equals(WebSqlContants.WEBSQL_PROCEDURE)) {
                        rs = metaData.getProcedures(dbName, null, "%");
                        while (rs.next()) {
                            String proceName = rs.getString("PROCEDURE_NAME");
                            Map<String, Object> map = Maps.newHashMap();
                            map.put("name", StringUtils.isBlank(proceName) ? "" : proceName);
                            map.put("content", "");
                            proMapLists.add(map);
                        }
                    } else if (objType.equals(WebSqlContants.WEBSQL_FUNCTION)) {
                        rs = metaData.getFunctions(dbName, null, "%");
                        while (rs.next()) {
                            String funcName = rs.getString("FUNCTION_NAME");
                            Map<String, Object> map = Maps.newHashMap();
                            map.put("name", StringUtils.isBlank(funcName) ? "" : funcName);
                            map.put("content", "");
                            proMapLists.add(map);
                        }
                    } else if (objType.equals(WebSqlContants.WEBSQL_TRIGGER)) {
                        String trigSql = "SELECT * FROM information_schema.TRIGGERS WHERE TRIGGER_SCHEMA='%s'";
                        rs = query(conn, String.format(trigSql, dbName));
                        while (rs.next()) {
                            String name = rs.getString("TRIGGER_NAME");
                            Map<String, Object> map = Maps.newHashMap();
                            map.put("name", StringUtils.isBlank(name) ? "" : name);
                            map.put("content", "");
                            proMapLists.add(map);
                        }
                    }
                } else {
                    if (objType.equals(WebSqlContants.WEBSQL_PROCEDURE)) {
                        String proceSql = "show create procedure %s";
                        rs = query(conn, String.format(proceSql, objName));
                        while (rs.next()) {
                            String content = rs.getString(3);
                            Map<String, Object> map = Maps.newHashMap();
                            map.put("name", objName);
                            map.put("content", StringUtils.isNoneBlank(content) ? content : "");
                            proMapLists.add(map);
                        }
                    } else if (objType.equals(WebSqlContants.WEBSQL_FUNCTION)) {
                        String funcSql = "show create function %s";
                        rs = query(conn, String.format(funcSql, objName));
                        while (rs.next()) {
                            String content = rs.getString(3);
                            Map<String, Object> map = Maps.newHashMap();
                            map.put("name", objName);
                            map.put("content", StringUtils.isNoneBlank(content) ? content : "");
                            proMapLists.add(map);
                        }
                    } else if (objType.equals(WebSqlContants.WEBSQL_TRIGGER)) {
                        String trigSql = "SELECT * FROM information_schema.TRIGGERS WHERE TRIGGER_SCHEMA='%s' AND TRIGGER_NAME = '%s'";
                        rs = query(conn, String.format(trigSql, dbName, objName));
                        Map<String, Object> map = Maps.newHashMap();
                        while (rs.next()) {
                            String actionStatement = rs.getString("ACTION_STATEMENT");
                            String actionTiming = rs.getString("ACTION_TIMING");
                            String eventManipulation = rs.getString("EVENT_MANIPULATION");
                            String eventObjectTable = rs.getString("EVENT_OBJECT_TABLE");
                            map.put("name", objName);
                            map.put("actionStatement", StringUtils.isBlank(actionStatement) ? "" : actionStatement);
                            map.put("actionTiming", StringUtils.isBlank(actionStatement) ? "" : actionTiming);
                            map.put("eventManipulation",
                                StringUtils.isBlank(eventManipulation) ? "" : eventManipulation);
                            map.put("eventObjectTable", StringUtils.isBlank(eventObjectTable) ? "" : eventObjectTable);
                        }
                        proMapLists.add(map);
                    }
                }
            } else {
                if (StringUtils.isBlank(objName)) {
                    String proceFuncNameSql = "SELECT UNIQUE(NAME) AS NAME FROM ALL_SOURCE WHERE OWNER='" + dbName
                                              + "' AND TYPE='" + objType + "'";
                    rs = query(conn, proceFuncNameSql);
                    while (rs.next()) {
                        String name = rs.getString("NAME");
                        Map<String, Object> map = Maps.newHashMap();
                        map.put("name", StringUtils.isBlank(name) ? "" : name);
                        map.put("content", "");
                        proMapLists.add(map);
                    }
                } else {
                    if (objType.equals(WebSqlContants.WEBSQL_TRIGGER)) {
                        String querySql = "SELECT OWNER,TRIGGER_NAME,TRIGGER_BODY,TRIGGER_TYPE,TRIGGERING_EVENT,TABLE_NAME FROM ALL_TRIGGERS WHERE OWNER='%s' AND TRIGGER_NAME='%s'";
                        rs = query(conn, String.format(querySql, dbName, objName));
                        Map<String, Object> map = Maps.newHashMap();
                        while (rs.next()) {
                            String actionStatement = rs.getString("TRIGGER_BODY");
                            String actionTiming = rs.getString("TRIGGER_TYPE");
                            String eventManipulation = rs.getString("TRIGGERING_EVENT");
                            String eventObjectTable = rs.getString("TABLE_NAME");
                            map.put("name", objName);
                            map.put("actionStatement", StringUtils.isBlank(actionStatement) ? "" : actionStatement);
                            map.put("actionTiming", StringUtils.isBlank(actionStatement) ? "" : actionTiming);
                            map.put("eventManipulation",
                                StringUtils.isBlank(eventManipulation) ? "" : eventManipulation);
                            map.put("eventObjectTable", StringUtils.isBlank(eventObjectTable) ? "" : eventObjectTable);
                        }
                        proMapLists.add(map);
                    } else {
                        StringBuilder sb = new StringBuilder();
                        String queryProceFuncContentSql = "SELECT * FROM ALL_SOURCE WHERE OWNER='%s' AND TYPE='%s' AND NAME='%s'";
                        rs = query(conn, String.format(queryProceFuncContentSql, dbName, objType, objName));
                        while (rs.next()) {
                            String proceText = rs.getString("TEXT");
                            sb.append(StringUtils.isBlank(proceText) ? "" : proceText).append(" ");
                        }
                        Map<String, Object> map = Maps.newHashMap();
                        map.put("name", objName);
                        map.put("content", sb.toString());
                        proMapLists.add(map);
                    }
                }
            }
            return proMapLists;
        } catch (Exception e) {
            throw new DataConnectionException(
                String.format("获取存储过程信息异常.请联系DBA核查.异常信息:%s", ExceptionUtils.getMessage(e)), e);
        } finally {
            DBUtil.closeDBResources(rs, null, conn);
        }
    }

    public static Map<String, Object> querySQLCounts(Connection conn, String sql,
                                                     DataBaseType baseType) throws SQLException {
        Map<String, Object> map = Maps.newHashMap();
        ResultSet rs = null;
        try {
            rs = query(conn, sql);
            while (rs.next()) {
                String value = rs.getString(1);
                map.put("counts", value);
            }
        } catch (SQLException e) {
            throw new DataConnectionException(String.format("获取表:%s的总数量失败.异常信息:%s", e), e);
        } finally {
            DBUtil.closeDBResources(rs, null, conn);
        }
        return map;
    }

    public static List<Map<String, String>> getSqlPlain(DataBaseType dataBaseType, Connection conn, String dbName,
                                                        String sql) {
        List<Map<String, String>> listMaps = new ArrayList<Map<String, String>>();
        ResultSet rs = null;
        String searchSql = null;
        try {
            if (DataBaseType.MySql == dataBaseType || DataBaseType.DRDS == dataBaseType) {
                searchSql = "EXPLAIN " + sql;
            } else if (DataBaseType.Oracle == dataBaseType) {
                return listMaps;
            }
            rs = query(conn, searchSql);
            while (rs.next()) {
                Map<String, String> columnMap = new HashMap<String, String>();
                columnMap.put("SELECT_TYPE", rs.getString("SELECT_TYPE"));
                columnMap.put("ROWS", rs.getString("ROWS"));
                columnMap.put("EXTRA", rs.getString("EXTRA"));
                columnMap.put("ID", rs.getString("ID"));
                columnMap.put("TABLE", rs.getString("TABLE"));
                columnMap.put("POSSIBLE_KEYS", rs.getString("POSSIBLE_KEYS"));
                columnMap.put("TYPE", rs.getString("TYPE"));
                columnMap.put("KEY_LEN", rs.getString("KEY_LEN"));
                columnMap.put("KEY", rs.getString("KEY"));
                columnMap.put("REF", rs.getString("REF"));
                listMaps.add(columnMap);
            }
            return listMaps;
        } catch (SQLException e) {
            throw new DataConnectionException(
                String.format("获取库:%s信息时失败.异常信息:%s", dbName, ExceptionUtils.getMessage(e)), e);
        } finally {
            DBUtil.closeDBResources(null, null, conn);
        }
    }

    public static Map<String, Object> getDataBaseInfo(DataBaseType dataBaseType, Connection conn) {
        Map<String, Object> versionMaps = new HashMap<String, Object>();
        try {
            DatabaseMetaData dmd = conn.getMetaData();
            versionMaps.put("productName", dmd.getDatabaseProductName());
            versionMaps.put("productVersion", dmd.getDatabaseProductVersion());
            versionMaps.put("driverName", dmd.getDriverName());
            versionMaps.put("driverVersion", dmd.getDriverVersion());
            versionMaps.put("transactionIsolation",
                TransactionIsolation.getTransactionIsolation(dmd.getDefaultTransactionIsolation()));
        } catch (SQLException e) {
            throw new DataConnectionException("获取库元信息时失败.异常信息:", e);
        } finally {
            DBUtil.closeDBResources(null, null, conn);
        }
        return versionMaps;
    }

    public static Map<String, Object> getDataBaseDetailViewInfo(DataBaseType dataBaseType, Connection conn,
                                                                String dbName, String viewName) {
        Map<String, Object> map = Maps.newHashMap();
        Statement statement = null;
        ResultSet rs = null;
        String queryColumnSql = null;
        String content = "";
        try {
            statement = conn.createStatement();
            if (dataBaseType == DataBaseType.Oracle) {
                queryColumnSql = "select text from all_views where OWNER = '" + dbName.toUpperCase()
                                 + "' and view_name ='" + viewName + "'";
            } else {
                queryColumnSql = "SELECT * FROM information_schema.views where table_name='" + viewName
                                 + "' and table_schema='" + dbName + "'";
            }
            rs = statement.executeQuery(queryColumnSql);
            while (rs.next()) {
                if (dataBaseType == DataBaseType.Oracle) {
                    content = rs.getString("TEXT");
                    map.put("content", content);
                } else {
                    content = rs.getString(4);
                    if (content.trim().startsWith("(") && content.trim().endsWith(")")) {
                        content = content.substring(1, content.length() - 1);
                    }
                    map.put("content", content);
                    String definer = rs.getString(7);
                    map.put("definer", definer);
                    String sqlSecurity = rs.getString(8);
                    map.put("sqlSecurity", sqlSecurity);
                    String checkOption = rs.getString(5);
                    map.put("checkOption", checkOption);
                }
            }
        } catch (Exception e) {
            throw new DataConnectionException(
                String.format("获取表:%s的视图详细信息失败.请联系DBA核查该库、视图表信息.异常信息:%s", viewName, ExceptionUtils.getMessage(e)), e);
        } finally {
            DBUtil.closeDBResources(rs, statement, conn);
        }
        return map;
    }

    /**
     * @return Left:ColumnName Middle:ColumnType Right:ColumnTypeName
     */
    public static Triple<List<String>, List<Integer>, List<String>> getColumnMetaData(Connection conn, String tableName,
                                                                                      String column) {
        Statement statement = null;
        ResultSet rs = null;

        Triple<List<String>, List<Integer>, List<String>> columnMetaData = new ImmutableTriple<List<String>, List<Integer>, List<String>>(
            new ArrayList<String>(), new ArrayList<Integer>(), new ArrayList<String>());
        try {
            statement = conn.createStatement();
            String queryColumnSql = "select " + column + " from " + tableName + " where 1=2";
            rs = statement.executeQuery(queryColumnSql);
            ResultSetMetaData rsMetaData = rs.getMetaData();
            for (int i = 0, len = rsMetaData.getColumnCount(); i < len; i++) {
                columnMetaData.getLeft().add(rsMetaData.getColumnName(i + 1));
                columnMetaData.getMiddle().add(rsMetaData.getColumnDisplaySize(i + 1));
                columnMetaData.getRight().add(rsMetaData.getColumnTypeName(i + 1));
            }
            return columnMetaData;

        } catch (SQLException e) {
            throw new DataConnectionException(String.format("获取表:%s字段的元信息时失败. 请联系 DBA 核查该库、表信息.", tableName), e);
        } finally {
            DBUtil.closeDBResources(rs, statement, null);
        }
    }

    public static boolean testConnWithoutRetry(DataBaseType dataBaseType, String url, String user, String pass,
                                               boolean checkSlave) {
        Connection connection = null;

        try {
            connection = connect(dataBaseType, url, user, pass);
            if (connection != null) {
                if (dataBaseType.equals(DataBaseType.MySql) && checkSlave) {
                    // dataBaseType.MySql
                    boolean connOk = !isSlaveBehind(connection);
                    return connOk;
                } else {
                    return true;
                }
            }
        } catch (Exception e) {
            throw new DataConnectionException("获取connection失败!", e);
        } finally {
            DBUtil.closeDBResources(null, connection);
        }
        return false;
    }

    public static boolean testConnWithoutRetry(DataBaseType dataBaseType, String url, String user, String pass,
                                               List<String> preSql) {
        Connection connection = null;
        try {
            connection = connect(dataBaseType, url, user, pass);
            if (null != connection) {
                for (String pre : preSql) {
                    if (doPreCheck(connection, pre) == false) {
                        LOG.warn("doPreCheck failed.");
                        return false;
                    }
                }
                return true;
            }
        } catch (Exception e) {
            LOG.warn("test connection of [{}] failed, for {}.", url, e.getMessage());
        } finally {
            DBUtil.closeDBResources(null, connection);
        }

        return false;
    }

    public static boolean isOracleMaster(final String url, final String user, final String pass) {
        try {
            return RetryUtil.executeWithRetry(new Callable<Boolean>() {

                @Override
                public Boolean call() throws Exception {
                    Connection conn = null;
                    try {
                        conn = connect(DataBaseType.Oracle, url, user, pass);
                        ResultSet rs = query(conn, "select DATABASE_ROLE from V$DATABASE");
                        if (DBUtil.asyncResultSetNext(rs, 5)) {
                            String role = rs.getString("DATABASE_ROLE");
                            return "PRIMARY".equalsIgnoreCase(role);
                        }
                        throw new DataConnectionException(
                            String.format("select DATABASE_ROLE from V$DATABASE failed,请检查您的jdbcUrl:%s.", url));
                    } finally {
                        DBUtil.closeDBResources(null, conn);
                    }
                }
            }, 3, 1000L, true);
        } catch (Exception e) {
            throw new DataConnectionException(
                String.format("select DATABASE_ROLE from V$DATABASE failed, url: %s", url), e);
        }
    }

    public static ResultSet query(Connection conn, String sql) throws SQLException {
        Statement stmt = conn.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY);
        // 默认3600 seconds
        stmt.setQueryTimeout(SOCKET_TIMEOUT_INSECOND);
        return query(stmt, sql);
    }

    public static int update(Connection conn, String sql) throws SQLException {
        Statement stmt = conn.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_UPDATABLE);
        // 默认3600 seconds
        stmt.setQueryTimeout(SOCKET_TIMEOUT_INSECOND);
        return update(stmt, sql);
    }

    // 创建存储过程
    public static int createProcedure(Connection conn, String sql) throws SQLException {
        PreparedStatement pstate = conn.prepareStatement(sql, ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_UPDATABLE);
        pstate.setQueryTimeout(SOCKET_TIMEOUT_INSECOND);
        return pstate.executeUpdate();
    }

    public static Map<String, Object> execPrepareCall(Connection conn, String sql) throws SQLException {
        Map<String, Object> map = Maps.newHashMap();
        // CallableStatement cstate = conn.prepareCall("{" + sql + "}");

        return map;
    }

    private static boolean doPreCheck(Connection conn, String pre) {
        ResultSet rs = null;
        try {
            rs = query(conn, pre);
            int checkResult = -1;
            if (DBUtil.asyncResultSetNext(rs)) {
                checkResult = rs.getInt(1);
                if (DBUtil.asyncResultSetNext(rs)) {
                    LOG.warn("pre check failed. It should return one result:0, pre:[{}].", pre);
                    return false;
                }
            }
            if (0 == checkResult) {
                return true;
            }
            LOG.warn("pre check failed. It should return one result:0, pre:[{}].", pre);
        } catch (Exception e) {
            LOG.warn("pre check failed. pre:[{}], errorMessage:[{}].", pre, e.getMessage());
        } finally {
            DBUtil.closeResultSet(rs);
        }
        return false;
    }

    public static void sqlValid(String sql, DataBaseType dataBaseType) {
        SQLStatementParser statementParser = SQLParserUtils.createSQLStatementParser(sql, dataBaseType.getTypeName());
        statementParser.parseStatementList();
    }

    /**
     * 异步获取resultSet的next(),注意，千万不能应用在数据的读取中。只能用在meta的获取
     * 
     * @param resultSet
     * @return
     */
    public static boolean asyncResultSetNext(final ResultSet resultSet) {
        return asyncResultSetNext(resultSet, 3600);
    }

    public static boolean asyncResultSetNext(final ResultSet resultSet, int timeout) {
        Future<Boolean> future = rsExecutors.get().submit(new Callable<Boolean>() {

            @Override
            public Boolean call() throws Exception {
                return resultSet.next();
            }
        });
        try {
            return future.get(timeout, TimeUnit.SECONDS);
        } catch (Exception e) {
            throw new DataConnectionException("异步获取ResultSet失败", e);
        }
    }

}
